package org.xawl.news.util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBUtil {
	
	

	public static Connection getConnection() {
		return JDBCUtil.getInstance().getConnection();
	}

	public static void free(Connection conn, Statement stmt, ResultSet rs) {
		JDBCUtil.getInstance().close(conn, stmt, rs);
	}
	
	public static void rollback(Connection conn) {
		JDBCUtil.getInstance().rollback(conn);
	}

	/**
	 * insert update delete SQL语句的执行的统一方法
	 * 
	 * @param sql SQL语句
	 * @param params 参数数组，若没有参数则为null
	 * @return 受影响的行数
	 */
	public static boolean executeUpdate(String sql, Object... params) {
		// 受影响的行数
		boolean flag = false;
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			// 获得连接
			conn = getConnection();
			//开启事务
			conn.setAutoCommit(false);
			// 调用SQL
			stmt = conn.prepareStatement(sql);
			// 参数赋值
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					stmt.setObject(i + 1, params[i]);
				}
			}
			stmt.executeUpdate();
			//try的最后提交事务 
			conn.commit();
			flag = true;
		} catch (SQLException e) {
			//回滚事务
			rollback(conn);
			e.printStackTrace();
		} finally {
			// 释放资源
			free(conn, stmt, null);
		}
		return flag;
	}

	public static List<Map<String, Object>> executeQuery(String sql, Object... params) {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Map<String, Object>> list = new ArrayList<>();
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					stmt.setObject(i + 1, params[i]);
				}
			}
			rs = stmt.executeQuery();
			ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<>();
				for (int i = 0; i < columnCount; i++) {
					String columnName = rsmd.getColumnLabel(i + 1);
					Object columnValue = rs.getObject(i + 1);
					// System.out.println(columnName + "===>" +
					// columnValue);
					map.put(columnName, columnValue);
				}
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 释放资源
			free(conn, stmt, rs);
		}
		return list;
	}
	
	@SuppressWarnings("unchecked")
	public static <T> T getObject(Class<?> clazz, String sql, Object... params) {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Object obj = null;
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			obj = clazz.newInstance();
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					stmt.setObject(i + 1, params[i]);
				}
			}
			rs = stmt.executeQuery();
			while (rs.next()) {
				// 创建一个clazz对象实例并将其赋给要返回的那个返回值。
                obj = clazz.newInstance();
                // 获取结果集的数据源
                ResultSetMetaData rsmd = rs.getMetaData();

                // 获取结果集中的字段数
                int count = rsmd.getColumnCount();

                // 循环取出个字段的名字以及他们的值并将其作为值赋给对应的实体对象的属性
                for (int i = 0; i < count; i++) {
                    // 获取字段名
                    String name = rsmd.getColumnName(i + 1);
                    // 利用反射将结果集中的字段名与实体对象中的属性名相对应，由于
                    // 对象的属性都是私有的所以要想访问必须加上getDeclaredField(name)和
                    Field f = obj.getClass().getDeclaredField(StringUtil.underline2Camel(name));
                    f.setAccessible(true);
                    // 将结果集中的值赋给相应的对象实体的属性
                    f.set(obj, rs.getObject(name));
                }
                return(T)obj;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 释放资源
			free(conn, stmt, rs);
		}
		return(T)obj;
	}
	
	@SuppressWarnings("unchecked")
	public static <T> List<T> getList(Class<?> clazz, String sql, Object... params) {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
        List<T> list = new ArrayList<>();
        T t = null;
        try {
        	conn = getConnection();
			stmt = conn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					stmt.setObject(i + 1, params[i]);
				}
			}
			rs = stmt.executeQuery();
            while (rs.next()) {
                // 创建一个clazz对象实例并将其赋给要返回的那个返回值。
                t = (T) clazz.newInstance();
                // 获取结果集的数据源
                ResultSetMetaData rsmd = rs.getMetaData();

                // 获取结果集中的字段数
                int count = rsmd.getColumnCount();

                // 循环取出个字段的名字以及他们的值并将其作为值赋给对应的实体对象的属性
                for (int i = 0; i < count; i++) {
                    // 获取字段名
                    String name = rsmd.getColumnName(i + 1);
                    // 利用反射将结果集中的字段名与实体对象中的属性名相对应，由于
                    // 对象的属性都是私有的所以要想访问必须加上getDeclaredField(name)和
                    Field f = t.getClass().getDeclaredField(StringUtil.underline2Camel(name));
                    f.setAccessible(true);
                    // 将结果集中的值赋给相应的对象实体的属性
                    f.set(t, rs.getObject(name));
                }
                list.add(t);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
        	// 释放资源
			free(conn, stmt, rs);
        }
        return list;
    }
	
	
	public static int getCount(String sql, Object... params) {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		int count = 0;
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					stmt.setObject(i + 1, params[i]);
				}
			}
			rs = stmt.executeQuery();
			while (rs.next()) {
				count = rs.getInt("count(*)");
				break;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 释放资源
			free(conn, stmt, rs);
		}
		return count;
	}
}